package com.hejia.alauda.persistence.mybatis;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.hejia.alauda.entity.Product;
import com.hejia.alauda.entity.User;
import com.hejia.alauda.persistence.BaseMapper;
import com.hejia.alauda.utils.Pager;

public interface ProductMapper extends BaseMapper<Product> {

	List<Product> findProductsByPage(
			@Param("params") Map<String, Object> params,
			@Param("pager") Pager<Product> pager);

	int updateAfterInsertOrder(@Param("amount") BigDecimal amount,
			@Param("id") Long id);

	@Update("update `product` set `soldamount`=IFNULL(`soldamount`,0)-#{amount},`unpayamount`=IFNULL(`unpayamount`,0)-#{amount},`modifyDate`=now() where id=#{id}")
	int updateAfterReleaseOrder(@Param("amount") BigDecimal amount,
			@Param("id") Long id);

	int updateDealExcessOrder(@Param("amount") BigDecimal amount,
			@Param("id") Long id);

	@Update("update `product` set `unpayamount`=IFNULL(`unpayamount`,0)-#{amount},`modifyDate`=now() where id=#{id}")
	int updateAfterPayOrder(@Param("amount") BigDecimal amount,
			@Param("id") Long id);
	
	
	

	/**
	 * 查询日期内的所有产品
	 * 
	 * @param statDate
	 * @param statDate2
	 * @return
	 */
	public List<Product> allProductByDate(String statDate, String statDate2);

	/**
	 * 查询产品v2版
	 * 
	 * @param params
	 * @param pager
	 * @return
	 */
	List<Product> findProducts(@Param("params") Map<String, Object> params,
			@Param("pager") Pager<Product> pager);

	/**
	 * 查询产品行数v2版
	 * 
	 * @param params
	 * @return
	 */
	int findProductsCount(@Param("params") Map<String, Object> params);

	/**
	 * 更新产品兑付时间
	 * 
	 * @param id
	 *            产品id
	 */
	@Update("update product a,(select #{id} as product_id,count(1) as c from financing where product_id=#{id} and status in('STATUS_DEFAULT','STATUS_INTEREST','STATUS_EXPIRED','STATUS_PAYAPPLY')) b set a.cashtime=now() where a.id=b.product_id and a.id=#{id} and b.c=0 and a.cashTime is null")
	int updateCashTime(@Param("id") Long id);

	@Update("UPDATE `product` SET `tag`=2 WHERE `tag`=1 AND `startTime`<=now() AND (`status` = 'PUBLISHED' or `status` = 'FINISHED')")
	int updateProductSale();

	@Update("UPDATE `product` SET `tag`=4 WHERE (endTime<now() OR (IFNULL(unpayAmount, 0) <= 0 AND totalAmount <= IFNULL(soldAmount, 0))) AND `tag`=2 AND (`status` = 'PUBLISHED' or `status` = 'FINISHED')")
	int updateProductSoldOut();

	@Update("UPDATE product a,(SELECT COUNT(b.id) AS c1,COUNT(CASE WHEN b.status=\'STATUS_DEFAULT\' THEN NULL WHEN b.status='STATUS_INTEREST' THEN NULL WHEN b.status='STATUS_EXPIRED' THEN NULL WHEN b.status='STATUS_PAYAPPLY' THEN NULL ELSE b.id END ) AS c2,b.`product_id` FROM financing b GROUP BY b.`product_id`) e SET a.`tag`=3 WHERE a.id=e.`product_id` AND a.`tag`=4 AND e.`c1`=e.`c2`")
	int updateProductHaveHonour();

	@Update("UPDATE `product` SET `numberOfShoppers`=IFNULL(`numberOfShoppers`,0)+1 WHERE `id`=#{id}")
	int updateNumberOfShoppers(Product product);

	@Select("SELECT `serial` FROM `product` WHERE (endTime<now() OR (IFNULL(unpayAmount, 0) <= 0 AND totalAmount <= IFNULL(soldAmount, 0))) AND `tag`=2 AND (`status` = 'PUBLISHED' or `status` = 'FINISHED')")
	@ResultType(value = Map.class)
	List<Map<String, String>> findProductSoldOut();

	/**
	 * 查询资产协议号产品部分
	 * @param year	年份
	 * @return		资产协议号产品部分列表		
	 */
	@Select("select agreementNumber from product where agreementNumber is not null and agreementNumber != '' and year(createDate) = #{year}")
	Set<String> findProductAgreementNumbersByYear(@Param("year") int year);

	/**
	 * 更新资产协议号产品部分
	 * @param id	产品ID
	 * @param agreementNumber	协议号
	 */
	@Select("update product set agreementNumber = #{agreementNumber} where id = #{id} and (agreementNumber is null || agreementNumber = '')")
	void updateProductAgreementNumber(@Param("id") Long id, @Param("agreementNumber") String agreementNumber);

	/**
	 * 查询资产协议号资产部分
	 * @param productId	产品ID
	 * @return	资产协议号资产部分列表
	 */
	@Select("select agreementNumber from financing where agreementNumber is not null and agreementNumber != '' and product_Id = #{productId}")
	Set<String> findFinancingAgreementNumbersByProductId(Long productId);

	/**
	 * 更新资产协议号资产部分
	 * @param id	资产ID
	 * @param agreementNumber	协议号
	 */
	@Select("update financing set agreementNumber = #{agreementNumber} where id = #{id} and (agreementNumber is null || agreementNumber = '')")
	void updateFinancingAgreementNumber(@Param("id") Long id, @Param("agreementNumber") String agreementNumber);

	
	@Select("select realName, idCard,sum(f.amount) as totalAmount,mobile from financing f, user u  where f.user_id=u.id and f.product_id=#{product_id} GROUP BY user_id")
	List<Map<String, Object>> findFinancialListByProduct(@Param("product_id")Long productId);
	
	@Select("select count(id) from product where status='PUBLISHED'")
	java.math.BigDecimal countProduct();


}
